import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import statsmodels.api as sm
from sklearn.decomposition import PCA

# 数据库配置
db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'root',
    'database': 'tushare',
    'port': 3306,
    'charset': 'utf8mb4'
}

# 连接数据库并读取数据
engine = create_engine(f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}")
conn = pymysql.connect(**db_config)
df = pd.read_sql_query("""
    SELECT d.* FROM date_1 d 
    WHERE d.trade_date BETWEEN '2023-01-01' AND '2023-12-31' 
    AND d.ts_code = '000001.SZ'
""", conn)

# 计算股票涨跌
df['zd_closes'] = round((df['closes'] - df['closes'].shift(1)) / df['closes'].shift(1), 2)
df['zs_closes'] = round((df['i_vloses'] - df['i_vloses'].shift(1)) / df['i_vloses'].shift(1), 2)
df['zs_vol'] = round((df['i_vol'] - df['i_vol'].shift(1)) / df['i_vol'].shift(1), 2)

# 处理缺失值
df = df.dropna(subset=['zd_closes', 'zs_closes', 'zs_vol'])

# 选择主成分分析的变量
X = df[['buy_vol', 'sell_vol', 'net_buy_vol', 'zs_closes']]

# 主成分分析
pca = PCA(n_components=3)
principal_components = pca.fit_transform(X)
principal_df = pd.DataFrame(principal_components, columns=['PC1', 'PC2', 'PC3'])

# 合并数据
data_pca = pd.concat([df, principal_df], axis=1)

# 构建回归模型
X_pca = data_pca[['PC1', 'PC2', 'PC3']]
X_pca = sm.add_constant(X_pca)
y = data_pca['zd_closes']

model_pca = sm.OLS(y, X_pca)
result_pca = model_pca.fit()

# 输出结果
print("主成分回归结果:")
print(result_pca.summary())